﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;

namespace HTComics.Data.Tools
{
    class DataResetter
    {
        private string _connString;

        public DataResetter(string connString)
        {
            _connString = connString;
        }

        public string  EmptyTables()
        {
            SqlConnection conn = new SqlConnection(_connString);            
            SqlCommand cmd;
            string exMsg = "";

            try
            {
                conn.Open();

                cmd = conn.CreateCommand();
                cmd.CommandText = "DELETE FROM TBL_Discount";
                cmd.ExecuteNonQuery();
                
                cmd = conn.CreateCommand();
                cmd.CommandText = "DELETE FROM TBL_PriceBands";
                cmd.ExecuteNonQuery();

                cmd = conn.CreateCommand();
                cmd.CommandText = "DELETE FROM TBL_PriceGuide";
                cmd.ExecuteNonQuery();

                cmd = conn.CreateCommand();
                cmd.CommandText = "DELETE FROM TBL_Feedback";
                cmd.ExecuteNonQuery();

                cmd = conn.CreateCommand();
                cmd.CommandText = "DELETE FROM TBL_OrderDetails";
                cmd.ExecuteNonQuery();

                cmd = conn.CreateCommand();
                cmd.CommandText = "DELETE FROM TBL_Order";
                cmd.ExecuteNonQuery();

                cmd = conn.CreateCommand();
                cmd.CommandText = "DELETE FROM TBL_Issue";
                cmd.ExecuteNonQuery();

                cmd = conn.CreateCommand();
                cmd.CommandText = "DELETE FROM TBL_Comic";
                cmd.ExecuteNonQuery();

                cmd = conn.CreateCommand();
                cmd.CommandText = "DELETE FROM TBL_User";
                cmd.ExecuteNonQuery();

                cmd = conn.CreateCommand();
                cmd.CommandText = "DELETE FROM TBL_Condition_Types";
                cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                exMsg = ex.Message;
            }
            finally
            {
                conn.Close();
            }

            return exMsg;
        }

        public string ReseedTableIdentities()
        {
            SqlConnection conn = new SqlConnection(_connString);
            SqlCommand cmd;
            string exMsg = "";

            try
            {
                conn.Open();
				
                cmd = conn.CreateCommand();
                cmd.CommandText = "DBCC CHECKIDENT (TBL_Condition_Types, RESEED, 0)";
                cmd.ExecuteNonQuery();

                cmd = conn.CreateCommand();
                cmd.CommandText = "DBCC CHECKIDENT (TBL_Discount, RESEED, 0)";
                cmd.ExecuteNonQuery();

                cmd = conn.CreateCommand();
                cmd.CommandText = "DBCC CHECKIDENT (TBL_Feedback, RESEED, 0)";
                cmd.ExecuteNonQuery();

                cmd = conn.CreateCommand();
                cmd.CommandText = "DBCC CHECKIDENT (TBL_User, RESEED, 0)";
                cmd.ExecuteNonQuery();

                cmd = conn.CreateCommand();
                cmd.CommandText = "DBCC CHECKIDENT (TBL_OrderDetails, RESEED, 0)";
                cmd.ExecuteNonQuery();

                cmd = conn.CreateCommand();
                cmd.CommandText = "DBCC CHECKIDENT (TBL_Order, RESEED, 0)";
                cmd.ExecuteNonQuery();

                cmd = conn.CreateCommand();
                cmd.CommandText = "DBCC CHECKIDENT (TBL_Issue, RESEED, 0)";
                cmd.ExecuteNonQuery();

                cmd = conn.CreateCommand();
                cmd.CommandText = "DBCC CHECKIDENT (TBL_Comic, RESEED, 0)";
                cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                exMsg = ex.Message;
            }
            finally
            {
                conn.Close();
            }

            return exMsg;
        }

        public  string InsertBasicData()
        {
            SqlConnection conn = new SqlConnection(_connString);
            SqlCommand cmd;
            string exMsg = "";
            string sql;

            try
            {
                conn.Open();

                sql = "INSERT INTO TBL_Condition_Types (Name, Code, Precedence, Factor) VALUES ('Near Mint +', 'NM+', 1, 170)";
                cmd = conn.CreateCommand();
                cmd.CommandText = sql;
                cmd.ExecuteNonQuery();

                sql = "INSERT INTO TBL_Condition_Types (Name, Code, Precedence, Factor) VALUES ('Near Mint', 'NM', 2, 140)";
                cmd = conn.CreateCommand();
                cmd.CommandText = sql;
                cmd.ExecuteNonQuery();

                sql = "INSERT INTO TBL_Condition_Types (Name, Code, Precedence, Factor) VALUES ('Near Mint -', 'NM-', 3, 100)";
                cmd = conn.CreateCommand();
                cmd.CommandText = sql;
                cmd.ExecuteNonQuery();

                sql = "INSERT INTO TBL_Condition_Types (Name, Code, Precedence, Factor) VALUES ('Very Fine +', 'VF+', 4, 85)";
                cmd = conn.CreateCommand();
                cmd.CommandText = sql;
                cmd.ExecuteNonQuery();

                sql = "INSERT INTO TBL_Condition_Types (Name, Code, Precedence, Factor) VALUES ('Very Fine', 'VF', 5, 75)";
                cmd = conn.CreateCommand();
                cmd.CommandText = sql;
                cmd.ExecuteNonQuery();

                sql = "INSERT INTO TBL_Condition_Types (Name, Code, Precedence, Factor) VALUES ('Very Fine -', 'VF-', 6, 66)";
                cmd = conn.CreateCommand();
                cmd.CommandText = sql;
                cmd.ExecuteNonQuery();

                sql = "INSERT INTO TBL_Condition_Types (Name, Code, Precedence, Factor) VALUES ('Fine +', 'F+', 7, 50)";
                cmd = conn.CreateCommand();
                cmd.CommandText = sql;
                cmd.ExecuteNonQuery();

                sql = "INSERT INTO TBL_Condition_Types (Name, Code, Precedence, Factor) VALUES ('Fine', 'FN', 8, 36)";
                cmd = conn.CreateCommand();
                cmd.CommandText = sql;
                cmd.ExecuteNonQuery();

                sql = "INSERT INTO TBL_Condition_Types (Name, Code, Precedence, Factor) VALUES ('Fine -', 'F-', 9, 30)";
                cmd = conn.CreateCommand();
                cmd.CommandText = sql;
                cmd.ExecuteNonQuery();

                sql = "INSERT INTO TBL_Condition_Types (Name, Code, Precedence, Factor) VALUES ('Very Good +', 'VG+', 10, 25)";
                cmd = conn.CreateCommand();
                cmd.CommandText = sql;
                cmd.ExecuteNonQuery();

                sql = "INSERT INTO TBL_Condition_Types (Name, Code, Precedence, Factor) VALUES ('Very Good', 'VG', 11, 20)";
                cmd = conn.CreateCommand();
                cmd.CommandText = sql;
                cmd.ExecuteNonQuery();

                sql = "INSERT INTO TBL_Condition_Types (Name, Code, Precedence, Factor) VALUES ('Very Good -', 'VG-', 12, 18)";
                cmd = conn.CreateCommand();
                cmd.CommandText = sql;
                cmd.ExecuteNonQuery();

                sql = "INSERT INTO TBL_Condition_Types (Name, Code, Precedence, Factor) VALUES ('Good +', 'GD+', 13, 15)";
                cmd = conn.CreateCommand();
                cmd.CommandText = sql;
                cmd.ExecuteNonQuery();

                sql = "INSERT INTO TBL_Condition_Types (Name, Code, Precedence, Factor) VALUES ('Good', 'GD', 14, 10)";
                cmd = conn.CreateCommand();
                cmd.CommandText = sql;
                cmd.ExecuteNonQuery();

                sql = "INSERT INTO TBL_Condition_Types (Name, Code, Precedence, Factor) VALUES ('Good -', 'GD-', 15, 8)";
                cmd = conn.CreateCommand();
                cmd.CommandText = sql;
                cmd.ExecuteNonQuery();

                sql = "INSERT INTO TBL_Condition_Types (Name, Code, Precedence, Factor) VALUES ('Fair', 'FR', 16, 5)";
                cmd = conn.CreateCommand();
                cmd.CommandText = sql;
                cmd.ExecuteNonQuery();

                sql = "INSERT INTO TBL_Condition_Types (Name, Code, Precedence, Factor) VALUES ('Poor', 'P', 17, 3)";
                cmd = conn.CreateCommand();
                cmd.CommandText = sql;
                cmd.ExecuteNonQuery();

                sql = "INSERT INTO TBL_Discount (Description, DiscountFactor, Enabled, TimeStamp) VALUES ('Current Global Discount', 20, 1, GETUTCDATE())";
                cmd = conn.CreateCommand();
                cmd.CommandText = sql;
                cmd.ExecuteNonQuery();

                sql = "INSERT INTO TBL_PriceBands (BandId, RangeStart, RangeEnd) VALUES (1, 0, 450)";
                cmd = conn.CreateCommand();
                cmd.CommandText = sql;
                cmd.ExecuteNonQuery();

                sql = "INSERT INTO TBL_PriceBands (BandId, RangeStart, RangeEnd) VALUES (2, 451, 1000000)";
                cmd = conn.CreateCommand();
                cmd.CommandText = sql;
                cmd.ExecuteNonQuery();

                sql = "INSERT INTO TBL_PriceGuide (GuideId, ConditionId_1,  ConditionId_2, Factor, Var_A, Var_B, BandId) VALUES (1,1,3,1.79,NULL,NULL,1)";
                cmd = conn.CreateCommand();
                cmd.CommandText = sql;
                cmd.ExecuteNonQuery();

                sql = "INSERT INTO TBL_PriceGuide (GuideId, ConditionId_1,  ConditionId_2, Factor, Var_A, Var_B, BandId) VALUES (2,2,3,1.45,NULL,NULL,1)";
                cmd = conn.CreateCommand();
                cmd.CommandText = sql;
                cmd.ExecuteNonQuery();

                sql = "INSERT INTO TBL_PriceGuide (GuideId, ConditionId_1,  ConditionId_2, Factor, Var_A, Var_B, BandId) VALUES (3,3,3,1,NULL,NULL,1)";
                cmd = conn.CreateCommand();
                cmd.CommandText = sql;
                cmd.ExecuteNonQuery();

                sql = "INSERT INTO TBL_PriceGuide (GuideId, ConditionId_1,  ConditionId_2, Factor, Var_A, Var_B, BandId) VALUES (4,4,5,1.25,NULL,NULL,1)";
                cmd = conn.CreateCommand();
                cmd.CommandText = sql;
                cmd.ExecuteNonQuery();

                sql = "INSERT INTO TBL_PriceGuide (GuideId, ConditionId_1,  ConditionId_2, Factor, Var_A, Var_B, BandId) VALUES (5,5,NULL,NULL,0.45621543,1.2110438,1)";
                cmd = conn.CreateCommand();
                cmd.CommandText = sql;
                cmd.ExecuteNonQuery();

                sql = "INSERT INTO TBL_PriceGuide (GuideId, ConditionId_1,  ConditionId_2, Factor, Var_A, Var_B, BandId) VALUES (6,6,5,0.9,NULL,NULL,1)";
                cmd = conn.CreateCommand();
                cmd.CommandText = sql;
                cmd.ExecuteNonQuery();

                sql = "INSERT INTO TBL_PriceGuide (GuideId, ConditionId_1,  ConditionId_2, Factor, Var_A, Var_B, BandId) VALUES (7,7,8,1.25,NULL,NULL,1)";
                cmd = conn.CreateCommand();
                cmd.CommandText = sql;
                cmd.ExecuteNonQuery();

                sql = "INSERT INTO TBL_PriceGuide (GuideId, ConditionId_1,  ConditionId_2, Factor, Var_A, Var_B, BandId) VALUES (8,8,NULL,NULL,0.24628138,0.94761213,1)";
                cmd = conn.CreateCommand();
                cmd.CommandText = sql;
                cmd.ExecuteNonQuery();

                sql = "INSERT INTO TBL_PriceGuide (GuideId, ConditionId_1,  ConditionId_2, Factor, Var_A, Var_B, BandId) VALUES (9,9,8,0.9332,NULL,NULL,1)";
                cmd = conn.CreateCommand();
                cmd.CommandText = sql;
                cmd.ExecuteNonQuery();

                sql = "INSERT INTO TBL_PriceGuide (GuideId, ConditionId_1,  ConditionId_2, Factor, Var_A, Var_B, BandId) VALUES (10,10,11,1.25,NULL,NULL,1)";
                cmd = conn.CreateCommand();
                cmd.CommandText = sql;
                cmd.ExecuteNonQuery();

                sql = "INSERT INTO TBL_PriceGuide (GuideId, ConditionId_1,  ConditionId_2, Factor, Var_A, Var_B, BandId) VALUES (11,11,NULL,NULL,0.15940405,0.46081039,1)";
                cmd = conn.CreateCommand();
                cmd.CommandText = sql;
                cmd.ExecuteNonQuery();

                sql = "INSERT INTO TBL_PriceGuide (GuideId, ConditionId_1,  ConditionId_2, Factor, Var_A, Var_B, BandId) VALUES (12,12,11,0.95,NULL,NULL,1)";
                cmd = conn.CreateCommand();
                cmd.CommandText = sql;
                cmd.ExecuteNonQuery();

                sql = "INSERT INTO TBL_PriceGuide (GuideId, ConditionId_1,  ConditionId_2, Factor, Var_A, Var_B, BandId) VALUES (13,13,14,1.25,NULL,NULL,1)";
                cmd = conn.CreateCommand();
                cmd.CommandText = sql;
                cmd.ExecuteNonQuery();

                sql = "INSERT INTO TBL_PriceGuide (GuideId, ConditionId_1,  ConditionId_2, Factor, Var_A, Var_B, BandId) VALUES (14,14,NULL,NULL,0.075156072,0.25440597,1)";
                cmd = conn.CreateCommand();
                cmd.CommandText = sql;
                cmd.ExecuteNonQuery();

                sql = "INSERT INTO TBL_PriceGuide (GuideId, ConditionId_1,  ConditionId_2, Factor, Var_A, Var_B, BandId) VALUES (15,15,14,0.9,NULL,NULL,1)";
                cmd = conn.CreateCommand();
                cmd.CommandText = sql;
                cmd.ExecuteNonQuery();

                sql = "INSERT INTO TBL_PriceGuide (GuideId, ConditionId_1,  ConditionId_2, Factor, Var_A, Var_B, BandId) VALUES (16,16,14,0.5,NULL,NULL,1)";
                cmd = conn.CreateCommand();
                cmd.CommandText = sql;
                cmd.ExecuteNonQuery();

                sql = "INSERT INTO TBL_PriceGuide (GuideId, ConditionId_1,  ConditionId_2, Factor, Var_A, Var_B, BandId) VALUES (17,17,14,0.25,NULL,NULL,1)";
                cmd = conn.CreateCommand();
                cmd.CommandText = sql;
                cmd.ExecuteNonQuery();

                sql = "INSERT INTO TBL_PriceGuide (GuideId, ConditionId_1,  ConditionId_2, Factor, Var_A, Var_B, BandId) VALUES (18,1,3,1.7,NULL,NULL,2)";
                cmd = conn.CreateCommand();
                cmd.CommandText = sql;
                cmd.ExecuteNonQuery();

                sql = "INSERT INTO TBL_PriceGuide (GuideId, ConditionId_1,  ConditionId_2, Factor, Var_A, Var_B, BandId) VALUES (19,2,3,1.4,NULL,NULL,2)";
                cmd = conn.CreateCommand();
                cmd.CommandText = sql;
                cmd.ExecuteNonQuery();

                sql = "INSERT INTO TBL_PriceGuide (GuideId, ConditionId_1,  ConditionId_2, Factor, Var_A, Var_B, BandId) VALUES (20,3,3,1,NULL,NULL,2)";
                cmd = conn.CreateCommand();
                cmd.CommandText = sql;
                cmd.ExecuteNonQuery();

                sql = "INSERT INTO TBL_PriceGuide (GuideId, ConditionId_1,  ConditionId_2, Factor, Var_A, Var_B, BandId) VALUES (21,4,5,1.25,NULL,NULL,2)";
                cmd = conn.CreateCommand();
                cmd.CommandText = sql;
                cmd.ExecuteNonQuery();

                sql = "INSERT INTO TBL_PriceGuide (GuideId, ConditionId_1,  ConditionId_2, Factor, Var_A, Var_B, BandId) VALUES (22,5,NULL,NULL,0.39921179,14.91059,2)";
                cmd = conn.CreateCommand();
                cmd.CommandText = sql;
                cmd.ExecuteNonQuery();

                sql = "INSERT INTO TBL_PriceGuide (GuideId, ConditionId_1,  ConditionId_2, Factor, Var_A, Var_B, BandId) VALUES (23,6,5,0.9,NULL,NULL,2)";
                cmd = conn.CreateCommand();
                cmd.CommandText = sql;
                cmd.ExecuteNonQuery();

                sql = "INSERT INTO TBL_PriceGuide (GuideId, ConditionId_1,  ConditionId_2, Factor, Var_A, Var_B, BandId) VALUES (24,7,8,1.25,NULL,NULL,2)";
                cmd = conn.CreateCommand();
                cmd.CommandText = sql;
                cmd.ExecuteNonQuery();

                sql = "INSERT INTO TBL_PriceGuide (GuideId, ConditionId_1,  ConditionId_2, Factor, Var_A, Var_B, BandId) VALUES (25,8,NULL,NULL,0.16916193,24.666433,2)";
                cmd = conn.CreateCommand();
                cmd.CommandText = sql;
                cmd.ExecuteNonQuery();

                sql = "INSERT INTO TBL_PriceGuide (GuideId, ConditionId_1,  ConditionId_2, Factor, Var_A, Var_B, BandId) VALUES (26,9,8,0.9332,NULL,NULL,2)";
                cmd = conn.CreateCommand();
                cmd.CommandText = sql;
                cmd.ExecuteNonQuery();

                sql = "INSERT INTO TBL_PriceGuide (GuideId, ConditionId_1,  ConditionId_2, Factor, Var_A, Var_B, BandId) VALUES (27,10,11,1.25,NULL,NULL,2)";
                cmd = conn.CreateCommand();
                cmd.CommandText = sql;
                cmd.ExecuteNonQuery();

                sql = "INSERT INTO TBL_PriceGuide (GuideId, ConditionId_1,  ConditionId_2, Factor, Var_A, Var_B, BandId) VALUES (28,11,NULL,NULL,0.12005121,11.265451,2)";
                cmd = conn.CreateCommand();
                cmd.CommandText = sql;
                cmd.ExecuteNonQuery();

                sql = "INSERT INTO TBL_PriceGuide (GuideId, ConditionId_1,  ConditionId_2, Factor, Var_A, Var_B, BandId) VALUES (29,12,11,0.95,NULL,NULL,2)";
                cmd = conn.CreateCommand();
                cmd.CommandText = sql;
                cmd.ExecuteNonQuery();

                sql = "INSERT INTO TBL_PriceGuide (GuideId, ConditionId_1,  ConditionId_2, Factor, Var_A, Var_B, BandId) VALUES (30,13,14,1.25,NULL,NULL,2)";
                cmd = conn.CreateCommand();
                cmd.CommandText = sql;
                cmd.ExecuteNonQuery();

                sql = "INSERT INTO TBL_PriceGuide (GuideId, ConditionId_1,  ConditionId_2, Factor, Var_A, Var_B, BandId) VALUES (31,14,NULL,NULL,0.057998231,5.881897,2)";
                cmd = conn.CreateCommand();
                cmd.CommandText = sql;
                cmd.ExecuteNonQuery();

                sql = "INSERT INTO TBL_PriceGuide (GuideId, ConditionId_1,  ConditionId_2, Factor, Var_A, Var_B, BandId) VALUES (32,15,14,0.9,NULL,NULL,2)";
                cmd = conn.CreateCommand();
                cmd.CommandText = sql;
                cmd.ExecuteNonQuery();

                sql = "INSERT INTO TBL_PriceGuide (GuideId, ConditionId_1,  ConditionId_2, Factor, Var_A, Var_B, BandId) VALUES (33,16,14,0.5,NULL,NULL,2)";
                cmd = conn.CreateCommand();
                cmd.CommandText = sql;
                cmd.ExecuteNonQuery();

                sql = "INSERT INTO TBL_PriceGuide (GuideId, ConditionId_1,  ConditionId_2, Factor, Var_A, Var_B, BandId) VALUES (34,17,14,0.25,NULL,NULL,2)";
                cmd = conn.CreateCommand();
                cmd.CommandText = sql;
                cmd.ExecuteNonQuery();

            }
            catch (Exception ex)
            {
                exMsg = ex.Message;
            }
            finally
            {
                conn.Close();
            }

            return exMsg;
        }
    }
}
